package com.wanmait.cookbook.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.wanmait.cookbook.vo.Cookbook;
import com.wanmait.cookbook.vo.Userinfo;
import com.wanmait.cookbook.vo.Works;
import com.wanmait.demo.util.DBHelper;

/*
 *  浣滃搧鏀惰棌琛―AO
 */
public class WorksCollectDAO {
	private WorksCollectDAO() {}
	private static WorksCollectDAO worksCollectDAO = new WorksCollectDAO();
	public static WorksCollectDAO getWorksCollectDAO() {
		return worksCollectDAO;
	}
	public int worksCollectCount(Works works) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT COUNT(*)  worksCount FROM works_collect WHERE works_id=?";
			conn = DBHelper.getHelper().getConn();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, works.getId());
			rs = ps.executeQuery();
			if (rs.next()) {
				int worksCollectCount = rs.getInt("worksCount");
				return worksCollectCount;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBHelper.getHelper().closeConn(rs, ps, conn);
		}

		return 0;
	}	
	
	//通锟斤拷锟矫伙拷id锟斤拷锟斤拷詹氐锟绞筹拷锟�
		public List<Works> getMyCollection(Integer userinfoId) {
			Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
			List<Works> workss = new ArrayList<>();
			String sql = "SELECT * from works,works_collect "
					+ "WHERE works_collect.userinfo_id=? "
					+ "AND works.id=works_collect.works_id";
			try {
				conn = DBHelper.getHelper().getConn();
				ps = conn.prepareStatement(sql);
				ps.setInt(1, userinfoId);
				rs = ps.executeQuery();
				while(rs.next()) {
					Works works=new Works();
					works=new Works();
					Userinfo userinfo=new Userinfo();
					works.setEnable(rs.getBoolean("works.enable"));
					works.setHot(rs.getBoolean("works.hot"));
					works.setId(rs.getInt("works.id"));
					works.setInfo(rs.getString("works.info"));
					works.setInputTime(rs.getTimestamp("works.input_time"));
					works.setTitle(rs.getString("title"));
					works.setPic(rs.getString("works.pic"));
					userinfo.setId(rs.getInt("works.userinfo_id"));
					works.setUserinfo(userinfo);
					workss.add(works);
				}
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DBHelper.getHelper().closeConn(rs, ps, conn);
			}
			return workss;
		}
		
		/**
		 *  娣诲姞鏀惰棌
		 * @param userinfoId
		 * @param cookbookId
		 */
		public void addCollection(Integer userinfoId,Integer worksId) {
			String sql = "INSERT INTO works_collect(userinfo_id,works_id) VALUES(?,?)";
			DBHelper.getHelper().executeUpdate(sql, userinfoId,worksId);
		}
		
		/**
		 *  鍙栨秷鏀惰棌
		 * @param userinfoId
		 * @param cookbookId
		 */
		public void cancelCollection(Integer userinfoId,Integer worksId) {
			String sql = "DELETE FROM works_collect WHERE userinfo_id=? AND works_id=?";
			DBHelper.getHelper().executeUpdate(sql, userinfoId,worksId);
		}
		
		public boolean judgeCollection(Integer userinfoId,Integer worksId) {
			Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
			String sql = "SELECT * from works_collect WHERE userinfo_id=? AND works_id=?";
			try {
				conn = DBHelper.getHelper().getConn();
				ps = conn.prepareStatement(sql);
				ps.setInt(1, userinfoId);
				ps.setInt(2, worksId);
				rs = ps.executeQuery();
				return rs.next();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DBHelper.getHelper().closeConn(rs, ps, conn);
			}
			return false;
		}
		
		//根据作品id查找用户
		public Userinfo getUserinfoByWorksId(int id) {
			Userinfo userinfo=null;
			Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
			String sql="SELECT * FROM userinfo WHERE id=(SELECT userinfo_id FROM works WHERE id=?)";
			try {
				conn=DBHelper.getHelper().getConn();
				ps=conn.prepareStatement(sql);
				ps.setInt(1, id);
				rs=ps.executeQuery();
				if(rs.next()) {
					userinfo = new Userinfo();
					userinfo.setId(rs.getInt("id"));
					userinfo.setName(rs.getString("name"));
					userinfo.setAvatar(rs.getString("avatar"));
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				DBHelper.getHelper().closeConn(rs, ps, conn);
			}
			return userinfo;
		}
		
}
